Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Defining a temp-table

You define a temp-table using the DEFINE TEMP-TABLE statement. There are two basic ways to define the fields and indexes in the table. You can make the temp-table LIKE some single database table (or even like another temp-table that you’ve already defined), which gives it all the fields and indexes from the other table, or you can define fields and indexes individually. You can also do both, so that in a single statement you can define a temp-table to be LIKE another table but also to have additional fields and indexes. Here is the basic syntax for the statement:

DEFINE TEMP-TABLE temp-table-name   [ LIKE table-name [USE-INDEX index-name [ AS PRIMARY ] ] . . . ]   [ FIELD field-name { AS data-type | LIKE field-name } [ field-options ]    ]      .      .      .   [ INDEX index-name [ IS [ UNIQUE ] [ PRIMARY ] ]      { index-field [ ASCENDING | DESCENDING ] } . . .   ]

You can see a description of the entire statement in the online help or in OpenEdge Development: Progress 4GL Reference .

Defining fields for the temp-table

If you use the LIKE option on your temp-table definition, the temp-table inherits all the field definitions for all the fields in the other table it is defined to be LIKE. The definitions include all of these attributes:

The field name.
The field’s data type and, for an array field, its extent.
The field’s initial value.
The field’s label and column-label.
The field’s format and, for a DECIMAL field, the number of decimals.
The field’s help text, if any.
The field’s font and color.

Whether you use the LIKE option to base your temp-table on another table or not, you can also use the FIELD phrase to define one or more fields for the temp-table. If you use the LIKE option, then any fields you define with the FIELD phrase are additional fields beyond the fields inherited from the other table. If you don’t use the LIKE option, then these are the only fields in the temp-table.

For fields you define individually with the FIELD phrase, you must specify at least the field name and data type, or use the LIKE phrase to define the field to be LIKE a field from another table. If you use the LIKE phrase on an individual field, your field inherits all of its attributes by default. You can override any of the field attributes of an inherited field except for the name, data type, and array extent, by using the appropriate keywords such as LABEL, FORMAT, and INITIAL. You can find a complete description of these keywords under the Field Options topic in the online help. You can also define the same attributes for fields that aren’t inherited from another table using the same keywords on the FIELD phrase.

You can use the FIELD phrase for one of three purposes:

If you want to base your temp-table on another table, but you don’t want all of its fields or you want to change some of the field attributes, including even the field names, then you can name the individual fields using the FIELD phrase rather than making the whole table LIKE the other table. You then specify each field to be LIKE a field from the other table, possibly with a different name or modified attributes.

If you only need to change certain display attributes of some of the fields, but otherwise want to use all the fields from the other table, you can use the LIKE phrase on the temp-table definition to base it on the other table. You then modify the field attributes in the definition of the browse or frame fields where they are displayed. This makes your temp-table definition simpler than if you explicitly named each field just to change a few attributes of some of the fields.

If you want to base your temp-table on another table, but you want some additional fields from one or more other tables as well, then you can define the temp-table to be LIKE the other table that it uses all the fields from, and then add FIELD phrases for each field that comes from another related table. You can use the LIKE keyword on these additional fields to inherit their database attributes as well.
If you need fields in your table that are not based at all on specific database fields, then you define them with the FIELD phrase. Again, you can do this whether the basic temp-table definition is LIKE another table or not.

You can define temp-tables with all the Progress data types that you can use for database fields (CHARACTER, DATE, DECIMAL, INTEGER, LOGICAL, RECID, CLOB, BLOB, and RAW). Note that CLOBs and BLOBs were introduced to the Progress 4GL in OpenEdge Release 10.0A.

In addition, you can define a temp-table field to be of type ROWID, which is something you can’t do with fields in database tables. You could use such a field to store the RowID of a record you read from a database, in order to use the RowID to relocate the record later in the procedure.

Defining indexes for the temp-table

If you use the LIKE other-table option for the temp-table, your temp-table is based on the other table you name. In this case, the following index rules apply:

If you don’t specify any index information in the DEFINE TEMP-TABLE statement, the temp-table inherits all the index layouts defined for the other table.
If you specify one or more USE-INDEX options, the temp-table inherits only the indexes you name. If one of those indexes is the primary index of the other table, it becomes the primary index of the temp-table, unless you explicitly specify AS PRIMARY for another index you define.
If you specify one or more INDEX options in the definition, then the temporary table inherits only those indexes from the other table that you have named in a USE-INDEX phrase.

The primary index is the one Progress uses by default to access and order records. Progress determines the primary index in this way:

If you specify AS PRIMARY in a USE-INDEX phrase for an index inherited from the other table, then that is the primary index.
If you specify IS PRIMARY on an INDEX definition specific to the temp-table, then that becomes the primary index.
If you inherit the primary index from the other table, then that becomes the primary index for the temp-table.
The first index you specify in the temp-table definition, if any, becomes the primary index.
If you don’t specify any index information at all, then Progress creates a default primary index that sorts the records in the order in which they’re created.

If you do not use the LIKE option to use another table as the basis for your temp-table, then your temp-table only has indexes if you define them with the INDEX phrase in the definition.

You should use the same considerations you would use for a database table in determining what indexes, if any, to define or inherit for a temp-table. On the one hand, there is a small cost to creating index entries on update that could become measurable if you have multiple indexes with multiple fields, or if you are creating large numbers of records in a performance-intensive situation where many records are created between user actions. You shouldn’t create or use indexes your procedure won’t need.

On the other hand, if you know that you need to access the records in the temp-table based on specific field values, then you should create or inherit the right indexes to let Progress locate the records you need without having to read through the whole temp-table to find them. The OpenEdge database is tremendously efficient and you might find that it can locate specific records in your table based on nonindexed field values so quickly that you hardly notice the lack of an index for those fields. However, it is a good idea to define one or more indexes if your temp-table is going to have more than a handful of records and if you know which fields will be used to locate, filter, or sort records in the table. If your code doesn’t need to access the records in the temp-table in any order other than the order in which they were created, then you don’t need an index other than the default index Progress gives you automatically.

Temp-table scope

Generally speaking, the scope of a temp-table is the procedure in which it’s defined. In fact, you can only define a temp-table at the level of the whole procedure, not within an internal procedure. When the procedure terminates, any temp-tables defined in it are emptied and deleted. Likewise, a temp-table is visible only within the procedure that defines it. If that procedure passes the temp-table by value to another procedure, the other procedure has to have its own definition of the temp-table, and it obtains a copy of the temp-table when it receives it as an INPUT parameter.

Alternately, you can use optional syntax to share a temp-table reference between procedures. This is discussed in the "Using a temp-table as a parameter" section.

It is also possible to send a temp-table to another procedure that has no prior definition of the temp-table, but instead receives the definition along with the temp-table data, but this involves the use of dynamic temp-tables, which you’ll learn about in Chapter 20, " Creating and Using Dynamic Temp-tables and Browses."

There are keywords to define a temp-table to be SHARED between procedures, and also to make the scope and visibility of the temp-table GLOBAL to the entire session. Chapter 13, "Advanced Use of Procedures in Progress," discusses this since you need to think about the relationship between different procedures in an OpenEdge application to understand properly when you should and shouldn’t use shared or global objects such as temp-tables.

You can also pass the handle to a temp-table from one procedure to another within a session, to avoid actually copying the temp-table between procedures. Chapter 20, "Creating and Using Dynamic Temp-tables and Browses,"discusses this, along with other uses of handles. For the purposes of this introduction to temp-tables, think of them as being statically defined and scoped to a single procedure.

Temp-table buffers

When you reference a database table in a Progress procedure, Progress provides you with a default buffer with the same name as the table. In this way, when you write a statement, such as FIND FIRST Customer, you are actually referring to a buffer with the name Customer. You can, of course, define additional buffers explicitly that have different names from the database table.

The same is true of temp-tables. When you define a temp-table, Progress provides you with a default buffer of the same name. Just as for database tables, you can define additional buffers with other names if you like. When you refer to the temp-table name in a statement such as FIND FIRST ttCust, you are referring to the default buffer for the temp-table just as you would be for a database table.

There is a temp-table attribute, DEFAULT-BUFFER-HANDLE, that returns the handle of the default buffer.